/*
 * Powered By [rapid-framework]
 * Web Site: http://www.rapid-framework.org.cn
 * Google Code: http://code.google.com/p/rapid-framework/
 * Since 2008 - 2012
 */

package com.tech15.pub.dao;

import java.util.*;

import javacommon.base.*;
import javacommon.util.*;

import cn.org.rapid_framework.util.*;
import cn.org.rapid_framework.web.util.*;
import cn.org.rapid_framework.page.*;
import cn.org.rapid_framework.page.impl.*;

import com.tech15.pub.model.*;
import com.tech15.pub.dao.*;
import com.tech15.pub.service.*;
import com.tech15.pub.vo.query.*;

/**
 * @author 胡作正 email:zuozhenghu@sina.com
 * @version 1.0
 * @since 1.0
 */


import static cn.org.rapid_framework.util.ObjectUtils.*;
import org.springframework.stereotype.Repository;

@Repository
public class AttendanceDao extends BaseHibernateDao<Attendance,Long>{

	public Class getEntityClass() {
		return Attendance.class;
	}
	
	public Page findByPageRequest(PageRequest<Map> pageRequest) {
		//XsqlBuilder syntax,please see http://code.google.com/p/rapid-xsqlbuilder
		// [column]为字符串拼接, {column}为使用占位符. 以下为图方便采用sql拼接,适用性能要求不高的应用,使用占位符方式可以优化性能. 
		// [column] 为PageRequest.getFilters()中的key
		String sql = "select t from Attendance t where 1=1 "
				+ "/~ and t.userid = '[userid]' ~/"
				+ "/~ and t.regtime = '[regtime]' ~/"
				+ "/~ and t.isauto = '[isauto]' ~/"
				+ "/~ order by [sortColumns] ~/";
		return pageQuery(sql,pageRequest);
	}
	
	public Page findPage(AttendanceQuery query) {
		String sql = "select t.*,t1.username,t1.realname from Attendance t " +
				" left join sys_user t1 on t.userid=t1.userid " +
				" where t.regtime >= date '"+DateTools.getDisplayDate()+"' and t.regtime <= to_date('"+DateTools.getDisplayDate()+" 23:59:59','yyyy-mm-dd hh24:mi:ss') ";
				if(isNotEmpty(query.getYgbh())){
					sql += " and t1.username like '%"+query.getYgbh()+"%'";
				}
				if(isNotEmpty(query.getName())){
					sql += " and t1.realname like '%"+query.getName()+"%'";
				}
				sql += " order by nlssort(t1.realname,'NLS_SORT=SCHINESE_PINYIN_M') ";
			Map map = new HashMap();
			map.put("username", "String");
			map.put("realname", "String");
			map.put("regtime", "Timestamp");
			return pageSqlQuery(sql, query, map, Attendance.class);
	}
	
	public Page search(AttendanceQuery query) {
		String tj = " and to_char(t1.regtime,'yyyy-mm') = '"+DateTools.getDisplayDate("yyyy-MM")+"'";
		if(isNotEmpty(query.getRegtimeBegin())){
			tj += " and to_char(t1.regtime,'yyyy-mm') = '"+query.getRegtimeBegin()+"'";
		}
		String sql = "select t.* from ("+
					"select t.ygbh,t.name,t.deptname,"+
					"(select count(*) from attendance t1 left join sys_user t2 on t1.userid=t2.userid where t2.username=t.ygbh "+tj+") as zgts,"+
					"("+
					"(select count(*) from attendance t1 left join sys_user t2 on t1.userid=t2.userid where t2.username=t.ygbh "+tj+") * 10 "+
					") as cbje"+
					" from sys_yg t  " +
					") t where 1=1 ";
			if(isNotEmpty(query.getYgbh())){
				sql += " and t.ygbh like '%"+query.getYgbh()+"%'";
			}
			if(isNotEmpty(query.getName())){
				sql += " and t.name like '%"+query.getName()+"%'";
			}
			if(isNotEmpty(query.getDeptname())){
				sql += " and t.deptname like '%"+query.getDeptname()+"%'";
			}
		
		Map map = new HashMap();
		map.put("ygbh", "String");
		map.put("name", "String");
		map.put("deptname", "String");
		map.put("zgts", "Long");
		map.put("cbje", "Long");
		return pageSqlQuery(sql, query, map, Attendance.class);
	}
	
	public List<Attendance> searchExport(AttendanceQuery query) {
		String tj = " and to_char(t1.regtime,'yyyy-mm') = '"+DateTools.getDisplayDate("yyyy-MM")+"'";
		if(isNotEmpty(query.getRegtimeBegin())){
			tj += " and to_char(t1.regtime,'yyyy-mm') = '"+query.getRegtimeBegin()+"'";
		}
		String sql = "select t.* from ("+
					"select t.ygbh,t.name,t.deptname,"+
					"(select count(*) from attendance t1 left join sys_user t2 on t1.userid=t2.userid where t2.username=t.ygbh "+tj+") as zgts,"+
					"("+
					"(select count(*) from attendance t1 left join sys_user t2 on t1.userid=t2.userid where t2.username=t.ygbh "+tj+") * 10 "+
					") as cbje"+
					" from sys_yg t  " +
					") t where 1=1 ";
			if(isNotEmpty(query.getYgbh())){
				sql += " and t.ygbh like '%"+query.getYgbh()+"%'";
			}
			if(isNotEmpty(query.getName())){
				sql += " and t.name like '%"+query.getName()+"%'";
			}
			if(isNotEmpty(query.getDeptname())){
				sql += " and t.deptname like '%"+query.getDeptname()+"%'";
			}
		
		Map map = new HashMap();
		map.put("ygbh", "String");
		map.put("name", "String");
		map.put("deptname", "String");
		map.put("zgts", "Long");
		map.put("cbje", "Long");
		return findSqlAll(sql, map, Attendance.class);
	}
	

}
